﻿using CBS.Common;
using CBS.DB.CBSEntity;
using CBS.DB;
using CBS.Model.ManageModel;
using CBS.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using CBS.Model.UIModel;
using Microsoft.EntityFrameworkCore;
using System.Collections;
using MySqlConnector;
using System.Data.Common;

namespace CBS.APP
{
    public class TeacherApp
    {
        public static bool CreateTeacher(TeacherViewModel newTeacher)
        {
            using CBSContext cbs = new CBSContext();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var teacher = new cbs_teacher
                {
                    TeacherName = newTeacher.TeacherName,
                    PhoneNumber = newTeacher.PhoneNumber,
                };
                cbs.cbs_teacher.Add(teacher);
                cbs.SaveChanges();

                cbs.cbs_teacher_amount.Add(new cbs_teacher_amount { TeacherId = teacher.TeacherId, Balance = 0 });
                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        public static List<TeacherViewModel> GetList(string keyword, Pagination page)
        {
            string sql = "select a.*,b.Balance\r\nfrom cbs_teacher a\r\n\tleft join cbs_teacher_amount b on a.TeacherId = b.TeacherId\r\nwhere @keyword = '' or a.TeacherName like @keyword";
            MySqlParameter[] para =
            {
                new MySqlParameter("@keyword",$"%{keyword}%"),
            };

            return DBHelper.FindListOrderBy<TeacherViewModel>(sql, page, FromPositionInSql.First, para);

        }

        public static bool DeleteTeacher(int teacherId, out string msg)
        {
            msg = "";
            using CBSContext cbs = new CBSContext();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                if (cbs.cbs_course_plan.Any(t=>t.TeacherId == teacherId) 
                    || cbs.cbs_lesson.Any(t=>t.TeacherId == teacherId))
                {
                    msg = "数据已经被使用，不能删除。";
                    transaction.Rollback();
                    return false;
                }

                var teacher = cbs.cbs_teacher.FirstOrDefault(t => t.TeacherId == teacherId);
                if (teacher == null)
                {
                    msg = "未找到将要删除的数据，请重试。";
                    transaction.Rollback();
                    return false;
                }
                var teaAmount = cbs.cbs_teacher_amount.FirstOrDefault(t => t.TeacherId == teacher.TeacherId);
                if (teaAmount == null || teaAmount.Balance != 0)
                {
                    msg = "教师账户仍有余额，或者未找到教师账户，请重试。";
                    transaction.Rollback();
                    return false;
                }

                cbs.cbs_teacher_amount.Remove(teaAmount);

                cbs.cbs_teacher.Remove(teacher);

                cbs.SaveChanges();
                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }

        }

        public static bool BatchDeleteTeacher(string keyword)
        {
            using CBSContext cbs = new CBSContext();
            var transaction = cbs.Database.BeginTransaction();

            try
            {
                var teachers = cbs.cbs_teacher.Where(t => (t.TeacherName.Contains(keyword) || t.PhoneNumber.Contains(keyword))).ToList();

                if (teachers.Count == 0)
                {
                    transaction.Rollback();
                    return false;
                }

                var ids = teachers.Select(t => t.TeacherId).ToList();
                var teasAmount = cbs.cbs_teacher_amount.Where(t => ids.Contains(t.TeacherId)).ToList();
                var teasSemesterAmount = cbs.cbs_teacher_semester_amount.Where(t => ids.Contains(t.TeacherId)).ToList();
                if (teasAmount.Count == 0 || teasAmount.Exists(t => t.Balance != 0) || teasSemesterAmount.Exists(t=>t.Receivable > 0)) 
                {
                    transaction.Rollback();
                    return false;
                }

                cbs.cbs_teacher_amount.RemoveRange(teasAmount);
                cbs.cbs_teacher.RemoveRange(teachers);

                cbs.SaveChanges();
                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }


        }

        public static TeacherViewModel GetForm(int teacherId)
        {
            using (CBSContext cbs = new CBSContext())
            {
                var entity = cbs.cbs_teacher.FirstOrDefault(t => t.TeacherId == teacherId);

                return PropertyHelper.Assignment<TeacherViewModel, cbs_teacher>(entity);
            }
        }

        public static List<TeacherSelectModel> GetSelectList()
        {
            using CBSContext cbs = new();
            var list = (from s in cbs.cbs_teacher
                        select new TeacherSelectModel
                        {
                            TeacherId = s.TeacherId,
                            TeacherName = s.TeacherName
                        }).ToList();
            if (list != null)
            {
                return list;
            }
            else 
            {
                return new List<TeacherSelectModel>();
            }
        }

        public static bool AddRemuneration(TeacherRemunerationViewModel teacherRemuneration) 
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var img = new cbs_image { ImageData = teacherRemuneration.ImgData };
                cbs.cbs_image.Add(img);
                cbs.SaveChanges();

                var remuneration = new cbs_teacher_remuneration 
                {
                    EmbitterId = teacherRemuneration.EmbitterId,
                    PayeeId = teacherRemuneration.PayeeId,
                    Remuneration = teacherRemuneration.Remuneration,
                    RemunerationCode = teacherRemuneration.RemunerationCode,
                    RemunerationPicId = img.ImageId,
                    RemunerationSemester = teacherRemuneration.SemesterId,
                    RemunerationTime = teacherRemuneration.RemunerationTime,
                    RemunerationType = teacherRemuneration.RemunerationType,
                    Memo = teacherRemuneration.Memo,
                };
                cbs.cbs_teacher_remuneration.Add(remuneration);

                var embitterAmount = cbs.cbs_teacher_amount.FirstOrDefault(t=>t.TeacherId == teacherRemuneration.EmbitterId);
                var payeeAmount = cbs.cbs_teacher_amount.FirstOrDefault(t => t.TeacherId == teacherRemuneration.PayeeId);

                if (embitterAmount == null || payeeAmount == null)
                {
                    transaction.Rollback();
                    return false;
                }

                embitterAmount.Balance -= teacherRemuneration.Remuneration;     //汇款人
                payeeAmount.Balance += teacherRemuneration.Remuneration;    //收款人
                cbs.SaveChanges();

                var embitterSemesterAmount = cbs.cbs_teacher_semester_amount.FirstOrDefault(t => t.TeacherId == teacherRemuneration.EmbitterId && t.SemesterId == teacherRemuneration.SemesterId);
                var payeeSemesterAmount = cbs.cbs_teacher_semester_amount.FirstOrDefault(t => t.TeacherId == teacherRemuneration.PayeeId && t.SemesterId == teacherRemuneration.SemesterId);
                if (embitterSemesterAmount == null)
                {
                    embitterSemesterAmount = new cbs_teacher_semester_amount 
                    {
                        TeacherId = teacherRemuneration.EmbitterId,
                        SemesterId = teacherRemuneration.SemesterId,
                        Receivable = 0,
                        Reeipts = -teacherRemuneration.Remuneration
                    };
                    cbs.cbs_teacher_semester_amount.Add(embitterSemesterAmount);
                }
                else 
                {
                    embitterSemesterAmount.Reeipts -= teacherRemuneration.Remuneration;
                }
                cbs.SaveChanges();

                if (payeeSemesterAmount == null) 
                {
                    payeeSemesterAmount = new cbs_teacher_semester_amount
                    {
                        TeacherId = teacherRemuneration.EmbitterId,
                        SemesterId = teacherRemuneration.SemesterId,
                        Receivable = 0,
                        Reeipts = -teacherRemuneration.Remuneration
                    };
                    cbs.cbs_teacher_semester_amount.Add(payeeSemesterAmount);
                    
                }
                else 
                {
                    payeeSemesterAmount.Reeipts += teacherRemuneration.Remuneration;
                }
                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch 
            {
                transaction.Rollback();
                throw;
            }
        }

        public static bool DeleteRemuneration(int remunerationId)
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var remuneration = cbs.cbs_teacher_remuneration.FirstOrDefault(x => x.RemunerationId == remunerationId);
                if (remuneration == null) { transaction.Rollback(); return false; }

                //汇款人
                var embitterRemunerationSemester = cbs.cbs_teacher_semester_amount.FirstOrDefault(x => x.TeacherId == remuneration.EmbitterId && x.SemesterId == remuneration.RemunerationSemester);
                
                //收款人
                var payeeRemunerationSemester = cbs.cbs_teacher_semester_amount.FirstOrDefault(x => x.TeacherId == remuneration.PayeeId && x.SemesterId == remuneration.RemunerationSemester);
                if (embitterRemunerationSemester == null)
                {
                    embitterRemunerationSemester = new cbs_teacher_semester_amount { TeacherId = remuneration.EmbitterId, SemesterId = remuneration.RemunerationSemester, Receivable = 0, Reeipts = 0 };
                    cbs.cbs_teacher_semester_amount.Add(embitterRemunerationSemester);
                }
                else
                {
                    embitterRemunerationSemester.Reeipts += remuneration.Remuneration;
                }
                if (payeeRemunerationSemester == null)
                {
                    payeeRemunerationSemester = new cbs_teacher_semester_amount { TeacherId = remuneration.PayeeId, SemesterId = remuneration.RemunerationSemester, Receivable = 0, Reeipts = 0 };
                    cbs.cbs_teacher_semester_amount.Add(payeeRemunerationSemester);
                }
                else
                {
                    payeeRemunerationSemester.Reeipts -= remuneration.Remuneration;
                }
                cbs.SaveChanges();

                if (remuneration.RemunerationPicId != null)
                {
                    var img = cbs.cbs_image.FirstOrDefault(t => t.ImageId == remuneration.RemunerationPicId);
                    if (img != null)
                    {
                        cbs.cbs_image.Remove(img);
                    }
                }

                cbs.cbs_teacher_remuneration.Remove(remuneration);
                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        public static List<TeacherSemesterAmountViewModel> GetTeacherAmountSemesterList(int semesterId, string keyword, Pagination page)
        {
            string sql = "select a.*,b.TeacherName,c.SemesterName\r\nfrom cbs_teacher_semester_amount a\r\n\tleft join cbs_teacher b on a.TeacherId = b.TeacherId\r\n\tleft join cbs_semester c on a.SemesterId = c.SemesterId\r\nwhere (@semesterId = 0 or a.SemesterId = @semesterId)\r\nand (@keyword = '' or b.TeacherName like @keyword)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@keyword",$"%{keyword}%"),
            };

            return DBHelper.FindListOrderBy<TeacherSemesterAmountViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static TeacherSemesterAmountViewModel GetTeacherSemesterAmountForm(int teacherSemesterAmountId) 
        {
            using CBSContext cbs = new();

            var entity = cbs.cbs_teacher_semester_amount.FirstOrDefault(t => t.SemesterAmountId == teacherSemesterAmountId);
            if (entity == null)
            {
                return new TeacherSemesterAmountViewModel();
            }
            var stu = cbs.cbs_teacher.FirstOrDefault(t => t.TeacherId == entity.TeacherId);
            if (stu == null)
            {
                return new TeacherSemesterAmountViewModel();
            }

            var semester = cbs.cbs_semester.FirstOrDefault(t => t.SemesterId == entity.SemesterId);

            return new TeacherSemesterAmountViewModel
            {
                SemesterAmountId = entity.SemesterAmountId,
                TeacherId = entity.TeacherId,
                TeacherName = stu.TeacherName,
                SemesterId = entity.SemesterId,
                SemesterName = semester.SemesterName,
                Receivable = entity.Receivable,
                Reeipts = entity.Reeipts,
            };
        }

        public static bool UpdateTeacherSemesterAmount(TeacherSemesterAmountViewModel model) 
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var semesterAmount = cbs.cbs_teacher_semester_amount.FirstOrDefault(t => t.SemesterAmountId == model.SemesterAmountId);
                if (semesterAmount == null)
                {
                    transaction.Rollback();
                    return false;
                }

                semesterAmount.Receivable = model.Receivable;
                semesterAmount.Reeipts = model.Reeipts;

                cbs.cbs_teacher_semester_amount.Update(semesterAmount);
                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        public static List<TeacherRemunerationViewModel> GetTeacherRemunerationList(int semesterId, int embitterId, int payeeId, Pagination page) 
        {
            string sql = "select a.*,b.SemesterName,c.TeacherId EmbitterName,d.TeacherName PayeeName\r\nfrom cbs_teacher_remuneration a\r\n\tleft join cbs_semester b on a.RemunerationSemester = b.SemesterId\r\n\tleft join cbs_teacher c on a.EmbitterId = c.TeacherId\r\n\tleft join cbs_teacher d on a.PayeeId = d.TeacherId\r\nwhere (@semesterId = 0 or a.RemunerationSemester = @semesterId)\r\nand (@embitterId = 0 or a.EmbitterId = @embitterId)\r\nand (@payeeId = 0 or a.PayeeId = @payeeId)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@embitterId",embitterId),
                new MySqlParameter("@payeeId",payeeId)
            };

            return DBHelper.FindListOrderBy<TeacherRemunerationViewModel>(sql, page, FromPositionInSql.First, para);
        }
    }
}
